using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Workflows.Components;

namespace Workflows.Components.DB.Sql
{
    /// <summary></summary>
    [Serializable]
    public class SqlOutput:ResultBase 
    {
        private SqlOutputType _OutputType;
        /// <summary></summary>
        public SqlOutputType OutputType
        {
            get { return _OutputType; }
            set { _OutputType = value; }
        }

        private SimpleSqlParameter[] _ReturnParameters;
        /// <summary></summary>
        public SimpleSqlParameter[] ReturnParameters
        {
            get { return _ReturnParameters; }
            set { _ReturnParameters = value; }
        }

        private object _ScalarOutput;
        /// <summary></summary>
        public object ScalarOutput
        {
            get { return _ScalarOutput; }
            set { _ScalarOutput = value; }
        }

        private DataTable _TableOutput;
        /// <summary></summary>
        public DataTable TableOutput
        {
            get { return _TableOutput; }
            set { _TableOutput = value; }
        }

        /// <summary></summary>
        public SqlOutput():base(Guid.Empty,Guid.Empty)
        {
            this._OutputType = SqlOutputType.Nothing;
            this._ReturnParameters = null;
            this._ScalarOutput = null;
            this._TableOutput = null;
        }
        /// <summary></summary>
        public SqlOutput(Guid componentID, Guid jobTicketID, 
            SqlOutputType outputType,
            SimpleSqlParameter[] returnParameters,
            object scalarOutput,
            DataTable tableOutput) 
            : base(componentID, jobTicketID)
        {
            this._OutputType = outputType;
            this._ReturnParameters = returnParameters;
            this._ScalarOutput = scalarOutput;
            this._TableOutput = tableOutput;
        }
        /// <summary></summary>
        public override XmlNode Serialize(ref XmlDocument xDoc, ref XmlNode parentNode)
        {
            XmlNode xNode = XmlDataUtil.AddElement(ref xDoc, parentNode, "Output");
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "Type", this.GetType().FullName);
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "WorkerID", this.OwnerWorkerInstanceID.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "FinishTime", this.FinishTime.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc,xNode,"SqlOutputType",((int)this.OutputType).ToString());

            if(this.ReturnParameters !=null && this.ReturnParameters.Length>0)
            {
                XmlNode returnParamRootNode = XmlDataUtil.AddElement(ref xDoc, xNode, "ReturnParameters");
                foreach (SimpleSqlParameter sqlParam in this.ReturnParameters)
                {
                    XmlNode paramNode = XmlDataUtil.AddElement(ref xDoc, returnParamRootNode, "SqlParameter");
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode,"ParameterName",sqlParam.ParameterName);
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "DataType", ((int) sqlParam.DbType).ToString());
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "Direction", ((int) sqlParam.Direction).ToString());
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterValue",
                                                sqlParam.Value is DBNull ? string.Empty : sqlParam.Value.ToString());
                }
            }

            if(this.ScalarOutput !=null)
            {
                XmlNode scalarOutputNode = XmlDataUtil.AddElement(ref xDoc, xNode, "ScalarOutput");
                SimpleDataType scalarType =
                    SimpleDataTypeUtil.ToSimpleDataType(this.ScalarOutput.GetType());
                XmlDataUtil.UpdateAttribute(ref xDoc, scalarOutputNode, "Type", ((int) scalarType).ToString());
                XmlDataUtil.UpdateAttribute(ref xDoc,scalarOutputNode,"Value",this.ScalarOutput.ToString());
            }

            if(this.TableOutput !=null)
            {
                XmlNode tableOutputNode = XmlDataUtil.AddElement(ref xDoc, xNode, "TableOutput");
                XmlNode colRootNode = XmlDataUtil.AddElement(ref xDoc, tableOutputNode, "Columns");
                foreach(DataColumn col in this.TableOutput.Columns)
                {
                    XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, colRootNode, "Column");
                    XmlDataUtil.UpdateAttribute(ref xDoc,colNode,"ColumnName",col.ColumnName);
                    XmlDataUtil.UpdateAttribute(ref xDoc,colNode,"DataType",col.DataType.Name);
                }

                XmlNode rowRootNode = XmlDataUtil.AddElement(ref xDoc, tableOutputNode, "Rows");
                for(int i=0;i<this.TableOutput.Rows.Count;i++)
                {
                    XmlNode rowNode = XmlDataUtil.AddElement(ref xDoc, rowRootNode, "Row");
                    XmlDataUtil.UpdateAttribute(ref xDoc,rowNode,"RowIndex",i.ToString());
                    for(int k=0;k<this.TableOutput.Columns.Count;k++)
                    {
                        if(this.TableOutput.Rows[i][k] !=null)
                        {
                            XmlNode cellNode = XmlDataUtil.AddElement(ref xDoc, rowNode, "Cell");
                            XmlDataUtil.UpdateAttribute(ref xDoc,cellNode,"ColumnName",this.TableOutput.Columns[k].ColumnName);
                            XmlDataUtil.UpdateAttribute(ref xDoc,cellNode,"Value",this.TableOutput.Rows[i][k].ToString());
                        }
                    }
                }
            }

            return xNode;
        }
        /// <summary></summary>
        public override ResultBase Instantiate(XmlNode xNode)
        {
            if (xNode.Attributes.GetNamedItem("Type").Value == this.GetType().FullName)
            {
                SqlOutput output = new SqlOutput();
                output.OwnerWorkerInstanceID = new Guid(xNode.Attributes.GetNamedItem("WorkerID").Value);
                output.FinishTime = DateTime.Parse(xNode.Attributes.GetNamedItem("FinishTime").Value);
                output.OutputType = (SqlOutputType) int.Parse(xNode.Attributes.GetNamedItem("SqlOutputType").Value);

                List<SimpleSqlParameter> parameters = new List<SimpleSqlParameter>();
                XmlNodeList paramNodes = xNode.SelectNodes("ReturnParameters/SqlParameter");
                if(paramNodes !=null && paramNodes.Count>0)
                {
                    foreach (XmlNode paramNode in paramNodes)
                    {
                        SimpleSqlParameter sqlParam = new SimpleSqlParameter();
                        sqlParam.ParameterName = paramNode.Attributes.GetNamedItem("ParameterName").Value;
                        sqlParam.DbType = (DbType) int.Parse(paramNode.Attributes.GetNamedItem("DataType").Value);
                        sqlParam.Direction =
                            (ParameterDirection) int.Parse(paramNode.Attributes.GetNamedItem("Direction").Value);
                        string paramValue = paramNode.Attributes.GetNamedItem("ParameterValue").Value;
                        if (string.IsNullOrEmpty(paramValue))
                        {
                            sqlParam.Value = DBNull.Value;
                        }
                        else
                        {
                            sqlParam.Value = SqlDbTypeUtil.GetValueFromString(paramValue, sqlParam.DbType);
                        }
                        parameters.Add(sqlParam);
                    }
                }
                if(parameters.Count>0)
                {
                    output.ReturnParameters = parameters.ToArray();
                }
                else
                {
                    output.ReturnParameters = null;
                }

                output.ScalarOutput = null;
                XmlNode scalarNode = xNode.SelectSingleNode("//ScalarOutput");
                if(scalarNode !=null)
                {
                    SimpleDataType scalarType =
                        (SimpleDataType) int.Parse(scalarNode.Attributes.GetNamedItem("Type").Value);
                    output.ScalarOutput =
                        SimpleDataTypeUtil.ReadDbValue(scalarType, scalarNode.Attributes.GetNamedItem("Value").Value);
                }

                output.TableOutput = null;
                XmlNode tblNode = xNode.SelectSingleNode("//TableOutput");
                if(tblNode !=null)
                {
                    DataTable tbl=new DataTable();
                    XmlNodeList colNodes = tblNode.SelectNodes("Column");
                    foreach(XmlNode colNode in colNodes)
                    {
                        string colName = colNode.Attributes.GetNamedItem("ColumnName").Value;
                        Type colDataType = Type.GetType(colNode.Attributes.GetNamedItem("DataType").Value);
                        tbl.Columns.Add(colName, colDataType);
                    }

                    XmlNodeList rowNodes = tblNode.SelectNodes("Row");
                    if(rowNodes !=null && rowNodes.Count>0)
                    {
                        foreach(XmlNode rowNode in rowNodes)
                        {
                            DataRow dr = tbl.NewRow();
                            XmlNodeList cellNodes = rowNode.SelectNodes("Cell");
                            if(cellNodes !=null && cellNodes.Count>0)
                            {
                                foreach(XmlNode cellNode in cellNodes)
                                {
                                    string fieldName = cellNode.Attributes.GetNamedItem("ColumnName").Value;
                                    object fieldValue =
                                        Convert.ChangeType(cellNode.Attributes.GetNamedItem("Value").Value,
                                                           tbl.Columns[fieldName].DataType);
                                    dr[fieldName] = fieldValue;
                                }
                            }
                            tbl.Rows.Add(dr);
                        }
                    }
                    output.TableOutput = tbl;
                }

                return output;
            }
            else
            {
                throw new Exception("Unable to instantiate output: invalid type specified in xml node");
            }
        }
        /// <summary></summary>
        public override Dictionary<string, Type> GetBindableMemberNames(Type[] excludeMemberFromTypes)
        {
            Dictionary<string, Type> bindableMemberNames = base.GetBindableMemberNames(excludeMemberFromTypes);
            if (bindableMemberNames == null)
            {
                bindableMemberNames = new Dictionary<string, Type>();
            }
            switch(this.OutputType)
            {
                case SqlOutputType.Nothing:
                    break;
                case SqlOutputType.OutputParameters:
                    if(this.ReturnParameters !=null && this.ReturnParameters.Length>0)
                    {
                        foreach (SimpleSqlParameter sqlParam in this.ReturnParameters)
                        {
                            if(!bindableMemberNames.ContainsKey(sqlParam.ParameterName))
                            {
                                bindableMemberNames.Add(sqlParam.ParameterName,SqlDbTypeUtil.ToSystemType(sqlParam.DbType));
                            }
                        }
                    }
                    break;
                case SqlOutputType.Scalar:
                    bindableMemberNames.Add("QueryScalarOutput", this._ScalarOutput.GetType());
                    break;
                case SqlOutputType.Table:
                    bindableMemberNames.Add("QueryTableOutput",typeof(DataTable));
                    break;
                default:
                    break;
            }
            return bindableMemberNames;
        }
        /// <summary></summary>
        public override object ReadMemberValue(string memberName)
        {
            switch (this.OutputType)
            {
                case SqlOutputType.Nothing:
                    break;
                case SqlOutputType.OutputParameters:
                    if (this.ReturnParameters != null && this.ReturnParameters.Length > 0)
                    {
                        foreach (SimpleSqlParameter sqlParam in this.ReturnParameters)
                        {
                            if (sqlParam.ParameterName==memberName)
                            {
                                return sqlParam.Value;
                            }
                        }
                    }
                    break;
                case SqlOutputType.Scalar:
                    if (memberName == "QueryScalarOutput")
                    {
                        return this._ScalarOutput;
                    }
                    break;
                case SqlOutputType.Table:
                    if (memberName == "QueryTableOutput")
                    {
                        return this._TableOutput;
                    }
                    break;
                default:
                    break;
            }
            return base.ReadMemberValue(memberName);
        }
    }
}
